<?php
error_reporting(E_ALL);

ob_start();
print("The current time is: ".date("Y-m-d H:i:s",time())."\n");
if(time() > strtotime(date("Y-m-d $cachetime",time()))) {
	$tname = '`'.$newdbname.'`.`'.$todaytbl.'`';
}
else {
	$tname = '`'.$newdbname.'`.`'.$yesterdaytbl.'`';
}
$mslink = mssql_connect(base64_decode(MSSERVE),base64_decode(MSUSR),base64_decode(MSPSS));
mysql_query("CREATE DATABASE IF NOT EXISTS `$newdbname`",$GLOBALS['lflink']);
$sql = "SHOW TABLES FROM `$newdbname` LIKE 'courselist_".date("Y_m_d",time())."'";
$tableexists = false;
if($sql = mysql_query($sql,$GLOBALS['lflink'])) {
	$tableexists = ($rs = mysql_fetch_array($sql)) ? 1 : false;
	if($tableexists) {
		print('The table already exists.<br />');
	}
	else {
		print('The table '.$tname.' does not yet exist in the database '.$newdbname.'. We will now create it.');
	}
}
else {
	print('There was an error checking to see if the table exists or not. '.mysql_error());
}
if($tableexists !== 1) {
	$createsql = "CREATE TABLE \n$tname (\n";
	$cols = array();
	$sql = "SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Results' ORDER BY ORDINAL_POSITION ASC;";
	$sql = mssql_query($sql,$mslink);
	$i=0;
	while($r = mssql_fetch_assoc($sql)) {
		$cols[$r['COLUMN_NAME']] = $r['COLUMN_NAME'].' '.$r['DATA_TYPE'];
		$cols[$r['COLUMN_NAME']] .= (!emptyReturn(trim($r['CHARACTER_MAXIMUM_LENGTH']))) ? '('.$r['CHARACTER_MAXIMUM_LENGTH'].')' : '';
	}
	mssql_free_result($sql);
	$createsql .= implode(",\n",$cols);
	$createsql .= ", \nINDEX (STRM,ACADEMICSUBJECT,COURSE_TITLE_LONG,LOCATION), \nFULLTEXT(NOTETEXT)";
	$createsql .= "\n);";
	if(mysql_query($createsql,$GLOBALS['lflink'])) {
		$createsql = "ALTER TABLE $tname ADD PRIMARY KEY (CLASS_NBR, STRM)";
		if(mysql_query($createsql,$GLOBALS['lflink'])) {
			$tableexists = true;
		}
		else {
			print('There was an error trying to create the primary key. '.mysql_error().'<div>'.$createsql.'</div>');
		}
	}
	else {
		print('There was an error creating the table. '.mysql_error().'<div>'.$createsql.'</div>');
	}
}
if($tableexists === true) {
	if(!isset($cols) || empty($cols)) {
		$cols = array();
		$sql = "SHOW COLUMNS FROM $tname";
		if($sql = mysql_query($sql,$GLOBALS['lflink'])) {
			while($row = mysql_fetch_assoc($sql)) {
				$cols[$row['Field']] = $row['Type'];
			}
			mysql_free_result($sql);
		}
	}
	$curid = NULL;
	$insertsql = "INSERT INTO $tname (".implode(",\n",array_keys($cols)).") VALUES(\n";
	$insertlist = array();
	$sql = "SELECT INSTITUTION,
TERM,
ACADEMICSUBJECT,
XLATLONGNAME,
SUBJECT,
CATALOG_NBR,
COURSE_TITLE_LONG,
CLASS_NBR,
CLASS_SECTION,
CREDITS,
STND_MTG_PAT,
LUE,
STIME,
ETIME,
LOCATION,
FACILITY_ID,
INSTRUCTORNAME,
PRINT_AT,
CLASS_NOTE_NBR,
NOTETEXT,
SESSION_TYPE,
CLASS_MTG_NBR,
DYNDESCR,
ENRL_CAP,
ENRL_TOT,
SHORTCLASSDESCR,
CRSE_ID,
CRSE_OFFER_NBR,
STRM,
SESSION_CODE,
INSTRUCTION_MODE,
SDATE,
EDATE FROM Results WHERE STRM >= ".CURTERM." AND STRM <= ".MAXTERM." ORDER BY STRM,CLASS_NBR ASC";
	print("\nWe are preparing to run the query:\n".$sql);
	$sql = mssql_query($sql,$mslink);
	while($rs = mssql_fetch_assoc($sql)) {
		$insertvals = array();
		if($curid != $rs['CLASS_NBR']) {
			foreach(array_keys($cols) as $k) {
				$rs[$k] = (is_numeric($rs[$k])) ? $rs[$k] : "'".lfescape($rs[$k])."'";
				$insertvals[$k] = $rs[$k];
			}
			$insertlist[$rs['CLASS_NBR']] = implode(',',$insertvals);
			print("\nWe just added ".$rs['CLASS_NBR']." to the list of classes that will be inserted.");
		}
		else {
			print("\nWe did not add ".$rs['CLASS_NBR']." to the list of classes, because it supposedly already exists.");
		}
		$curid = $rs['CLASS_NBR'];
	}
	$insertsql .= implode("),\n(",$insertlist).")";
	mssql_free_result($sql);
	if(mysql_query($insertsql)) {
		echo 'The data was added to the table successfully.';
		if(mysql_query("DROP TABLE IF EXISTS ".str_replace($todaytbl,$yesterdaytbl,$tname),$GLOBALS['lflink'])) {
			echo "\nThe old table (".str_replace($todaytbl,$yesterdaytbl,$tname).") was successfully dropped.";
			if(file_exists($paths['htmlpath'].'sis-courses/cache') && is_dir($paths['htmlpath'].'sis-courses/cache')) {
				shell_exec('rm -Rf '.$paths['htmlpath'].'sis-courses/cache');
			}
		}
	}
	else {
		print('There was an error: '.mysql_error().'<div>'.$insertsql.'</div>');
	}
}
else {
	print('The table did not exist and was not created.');
}
mysql_select_db(base64_decode(DBNAME));
print("The current time is: ".date("Y-m-d H:i:s",time())."\n");
file_put_contents('/home/cgrymala/sisImport.log',ob_get_contents());
ob_end_clean();
?>
